import os
import pdb
import sys
import types
import urllib.request
import time
import datetime
import configparser
import xml.etree.ElementTree as xmltree
try:
    import kinterbasdb; kinterbasdb.init(type_conv=200)
except Exception as err:
    print('WARNING: can\'t load "kinterbasdb": %s' % (str(err)))
try:
    import pyodbc
    MSDBDriver = "pyodbc"
except:
    try:
        import pymssql
        MSDBDriver = "pymssql"
    except Exception as err:
        print('WARNING: can\'t load noone of "pyodbc" and "pymssql": %s' % str(err))


class SimpleLogger:
    FATAL = 1
    ERROR = 2
    WARNING = 3
    INFO = 4
    DEBUG = 5

    levels = \
    {
     FATAL     : 'FATAL',
     ERROR     : 'ERROR',
     WARNING   : 'WARNING',
     INFO      : 'INFO',
     DEBUG     : 'DEBUG'
    }
    DATEFORMAT = '%Y-%m-%dT%H:%M:%S'

    def __init__(self):
        self.flog   = None
        self.fpath  = None
        self.level  = SimpleLogger.INFO
        self.ident  = ''

    @staticmethod
    def formatmsg(level, msg):
            #time stamp
        stamp = datetime.datetime.now().strftime(SimpleLogger.DATEFORMAT)
        return '%s %s %s' % (stamp, str(level), str(msg))

    def setfile(self, path):
        try:
            self.flog = open(path, 'w')
            self.fpath = path
        except Exception as err:
            msg = formatmsg(SimpleLogger.FATAL, 'setfile: ' + str(err))
            print(msg)

    def setlevel(self, level):
        if level in SimpleLogger.levels.keys():
            self.level = level
        else:
            raise RuntimeError('SimpleLogger::formatmsg: unknown level(%s)' % str(level))

    def setident(self, ident):
        if not isinstance(ident, str): raise RuntimeError('SimpleLogger::setident: ident should be a string')
        self.ident = ident

    def log(self, level, msg):
        if self.level >= level:
            fullmsg = self.formatmsg(self.levels[level], self.ident + ' ' + msg)
            print(fullmsg)
            if self.flog != None:
                self.flog.write(fullmsg + '\n')
                self.flog.flush()

    def error(self, msg): self.log(SimpleLogger.ERROR, msg)
    def warning(self, msg): self.log(SimpleLogger.WARNING, msg)
    def info(self, msg): self.log(SimpleLogger.INFO, msg)
    def debug(self, msg): self.log(SimpleLogger.DEBUG, msg)

class Logs:
    ROOT = "root"
    OBJECTS = "objects"
    MSAPI = "msapi"
    MSDB = "msdb"
    CREDODB = "credodb"

    def __init__(self):
        self.logs = {}
    def at(self, key):
        if key not in self.logs:
            self.logs[key] = SimpleLogger()
        return self.logs[key]
    def setRootFile(self, fpath = None):
        if fpath == None:
            fpath = sys.argv[0] + '.log'
        self.at(Logs.ROOT).setfile(fpath)

    def gerror(self, msg): self.at(Logs.ROOT).error(msg)
    def gwarning(self, msg): self.at(Logs.ROOT).warning(msg)
    def ginfo(self, msg): self.at(Logs.ROOT).info(msg)
    def gdebug(self, msg): self.at(Logs.ROOT).debug(msg)

    def fatal(self, key, msg):
        if key in self.logs:
            self.logs[key].fatal(msg)
        else:
            self.at(Logs.ROOT).fatal(msg)

    def error(self, key, msg):
        if key in self.logs:
            self.logs[key].error(msg)
        else:
            self.gerror(msg)

    def warning(self, key, msg):
        if key in self.logs:
            self.logs[key].warning(msg)
    def info(self, key, msg):
        if key in self.logs:
            self.logs[key].info(msg)
    def debug(self, key, msg):
        if key in self.logs:
            self.logs[key].debug(msg)
glogger = Logs()

if os.name == 'posix':  g_dirsep = '/'
if os.name == 'nt':     g_dirsep = '\\'
class Config:
    @staticmethod
    def load(path):
        parser = ConfigParser.RawConfigParser()
        if os.path.exists(path): parser.read(path)
        return parser

class Object:
    def __init__(self):
        self.isTest = True
        self.isEnabled = False

        self.msid = 0
        self.targetid = ''
        self.skey = ''
        self.name = ''

        self.caronline_login = ''
        self.caronline_password = ''
        self.caronline_macid = ''

        self.events = {}
        
        self.can_milage = 0
        self.can_engine_rpm = 0
        self.can_engine_code = ''

class Objects:
    @staticmethod
    def fromFile(objs, path):
        '''
        file format:
        id=<targetid> name=<name> skey=<skey> ...
        [id=<targetid> name=<name> skey=<skey> ...]
        '''
        try:
            f = open(path, 'r')
            lines = f.readlines()
            glogger.debug(Logs.OBJECTS, 'fromFile: path(%s) lines(%d)' % (path, len(lines)))
        except Exception as err:
            glogger.error(Logs.OBJECTS, 'fromFile: %s' % str(err))

        for line in lines:
            try:
                parsed = {}
                buf = line.split(' ')
                for prop in buf:
                    prop = prop.split('=')
                    parsed[prop[0]] = prop[1]

                obj = Object()
                obj.targetid = parsed['id']
                obj.isEnabled = True
                if parsed.has_key('skey'): obj.skey = parsed['skey']
                if parsed.has_key('login'): obj.caronline_login = parsed['login']
                if parsed.has_key('password'): obj.caronline_password = parsed['password']

                objs[obj.targetid] = obj
                glogger.debug(Logs.OBJECTS, 'fromFile: id(%s) as %s' % (obj.targetid, str(parsed)))

            except Exception as err:
                glogger.warning(Logs.OBJECTS, 'fromFile: line(%s): %s' % (str(line), str(err)))

        glogger.info(Logs.OBJECTS, 'fromFile: path(%s) lines(%d) objects(%d)' % (path, len(lines), len(objs)))

    @staticmethod
    def filterNormalTest(objs, filterRulesFName):
        #filterRulesFile contains PCN IDs of NORMAL objects
        try:
            f = open(filterRulesFName, 'r')
        except:
            glogger.warning(Logs.OBJECTS, 'filterNormalTest: can\'t open file(%s) - all are test' % filterRulesFName)
            for k,o in objs.iteritems(): o.isTest = True
            return

        idsnorm = []
        for idx in f.readlines():
            idx = idx.strip()
            if len(idx): idsnorm.append(idx)
        glogger.debug(Logs.OBJECTS, 'filterNormalTest: loaded %d ids from file(%s)' % (len(idsnorm), filterRulesFName))

        norm = 0
        test = 0
        for o in objs.values():
            if str(o.targetid) in idsnorm:
                o.isTest = False
                norm += 1
            else:
                o.isTest = True
                test += 1
        glogger.info(Logs.OBJECTS, 'filterNormalTest: normal(%d) test(%d)' % (norm, test))
    @staticmethod
    def makeTest(filterRulesFName, pcnid):
        try:
            f = open(filterRulesFName, 'r')
        except:
            glogger.error(Logs.OBJECTS, 'makeTest: can\'t open file(%s)' % filterRulesFName)

        idsnorm = []
        for idx in f.readlines():
            idx = idx.strip()
            if len(idx): idsnorm.append(idx)

        if pcnid in idsnorm: idsnorm.remove(pcnid)

        f = open(filterRulesFName + '.save', 'w')
        idsnorm.sort()
        for oid in idsnorm: f.write('%s\n' % oid)
        f.close()

        os.unlink(filterRulesFName)
        os.rename(filterRulesFName + '.save', filterRulesFName)
        glogger.info(Logs.OBJECTS, 'makeTest: object of pcnid(%s) is TEST now' % str(pcnid))
        return
    @staticmethod
    def makeNormal(filterRulesFName, pcnid):
        try:
            f = open(filterRulesFName, 'r')
        except:
            glogger.error(Logs.OBJECTS, 'makeNormal: can\'t open file(%s)' % filterRulesFName)

        idsnorm = []
        for idx in f.readlines():
            idx = idx.strip()
            if len(idx): idsnorm.append(idx)

        if pcnid not in idsnorm: idsnorm.append(pcnid)

        f = open(filterRulesFName + '.save', 'w')
        idsnorm.sort()
        for oid in idsnorm: f.write('%s\n' % oid)
        f.close()

        os.unlink(filterRulesFName)
        os.rename(filterRulesFName + '.save', filterRulesFName)
        glogger.info(Logs.OBJECTS, 'makeNormal: object of pcnid(%s) is NORMAL now' % str(pcnid))
        return

    @staticmethod
    def iterateNormal(objs):
        for o in objs.values():
            if o.isEnabled and not o.isTest: yield o
            continue
    @staticmethod
    def iterateTest(objs):
        for o in objs.values():
            if o.isEnabled and o.isTest: yield o
            continue
    @staticmethod
    def iterateDeleted(objs):
        for o in objs.values():
            if not o.isEnabled: yield o
            continue
    @staticmethod
    def iterateAll(objs):
        for o in objs.values():
            yield o

    def __init__(self):
        self.objs = {}

    def all(self):
        for o in Objects.iterateAll(self.objs):
            yield o
    def deleted(self):
        for o in Objects.iterateDeleted(self.objs):
            yield o
    def test(self):
        for o in Objects.iterateTest(self.objs):
            yield o
    def normal(self):
        for o in Objects.iterateNormal(self.objs):
            yield o

    def get(self, pcnid):
        found = []
        for o in self.all():
            if int(o.targetid) == int(pcnid):
                found.append(o)
        if len(found) == 0: raise RuntimeError('object(%d) is not found' % int(pcnid))

        for o in found:
            if o.isEnabled: return o
        return found[0]

    def filter(self, path): return Objects.filterNormalTest(self.objs, path)

class CarOnlineDB_Templates:
    def objectsLoad(self, dbconn, objs, prefixes):
        dbcursor = dbconn.cursor()
        try:
            query = 'SELECT id,uid,lastEventTime,lastEventType,versionSoftPGSM FROM _devices'
            dbcursor.execute(query)
            uids = dbcursor.fetchall()
            enabled = 0
            for uid in uids:
                try:
                    query = 'SELECT login,password FROM _users WHERE defaultDevice=%s' % str(uid[0])
                    dbcursor.execute(query)
                    devdata = dbcursor.fetchall()
                    pcnid   = str(devdata[0][0])
                    found = False
                    obj = Object()
                    for p in prefixes:
                        if pcnid.find(p) != -1:
                            found = True
                            pcnid   = pcnid.replace(p,'')
                            pcnid   = pcnid.replace(' ','')
                            if p == 'dev':
                                obj.isEnabled = True
                                enabled += 1
                    if not found:
                        glogger.debug(Logs.OBJECTS, 'fromMSDB: msid(%s) login(%s) ignored' % (str(uid[0]), pcnid))
                        continue
                    obj.msid = int(uid[0])
                    obj.caronline_macid = str(uid[1])
                    obj.targetid = int(pcnid)
                    obj.caronline_login = str(devdata[0][0])
                    obj.caronline_password = str(devdata[0][1])
                    try:
                        obj.lastEventTime = uid[2]
                        obj.lastEventType = int(uid[3])
                        obj.versionSoftPGSM = str(uid[4])
                    except Exception as err:
                        glogger.debug(Logs.OBJECTS, 'fromMSDB: msid(%d) pcnid(%d) extra params: %s' % (obj.msid, obj.targetid, str(err)))
                        obj.lastEventTime = datetime.datetime(2012,1,1)
                        obj.lastEventType = 0
                        obj.versionSoftPGSM = 'Undefined'
                    objs.objs[obj.msid] = obj
                    glogger.debug(Logs.OBJECTS,'fromMSDB: msid(%d) pcnid(%d) login(%s)' % (obj.msid, obj.targetid, obj.caronline_login))
                except Exception as err:
                    glogger.warning(Logs.OBJECTS, 'fromMSDB: msid(%s): %s' % (str(uid[0]), str(err)))
                    continue
            glogger.info(Logs.OBJECTS, 'fromMSDB: objects(%d/%d)' % (enabled, len(objs.objs)))
            return True
        except Exception as err:
            glogger.error(Logs.OBJECTS, 'fromMSDB: %s' % str(err))

    def objectRename(self, dbconn, oldlogin, newlogin):
        cursor = dbconn.cursor()
        oldlogin = str(oldlogin)
        newlogin = str(newlogin)
        query = "UPDATE _users_internal SET login='%s' WHERE login='%s'" % (newlogin, oldlogin)
        cursor.execute(query)
        dbconn.commit()
        return True

    def objectCreate(self, dbconn, pcnid, mac):
        cursor = dbconn.cursor()
        login   = 'dev' + str(pcnid)
        pwd     = 'pass' + str(pcnid)
        query   = "SELECT login,password FROM _users_internal WHERE login='%s'" % (login)
        glogger.debug(Logs.OBJECTS, 'registrateObjectInMSDB:%s: query1: %s' % (pcnid, query))
        cursor.execute(query)
        t = cursor.fetchall()
        if len(t) > 0 and t[0][0] == login: raise RuntimeError('device %s already exists!' % (login))

        query   = "INSERT INTO _devices_internal (uid, ServerCode) VALUES ('%s', '1')" % (str(mac))
        glogger.debug(Logs.OBJECTS, 'registrateObjectInMSDB:%s: query2: %s' % (pcnid, query))
        cursor.execute(query)
        dbconn.commit()

        cursor  = dbconn.cursor()
        query   = "SELECT IDENT_CURRENT('_devices_internal')"
        glogger.debug(Logs.OBJECTS, 'registrateObjectInMSDB:%s: query3: %s' % (pcnid, query))
        cursor.execute(query)
        t = cursor.fetchall()
        msid = int(t[0][0])

        query   = "INSERT INTO _users_internal (login, password, defaultDevice) VALUES ('%s', '%s', '%s')" % (login, pwd, str(msid))
        glogger.debug(Logs.OBJECTS, 'registrateObjectInMSDB:%s: query4: %s' % (pcnid, query))
        cursor.execute(query)
        dbconn.commit()

        device_value = 'login=%s&password=%s&exportSupport=1' % (login, pwd)
        device_ctime = datetime.datetime.fromtimestamp(time.time()).strftime('%Y%m%d %H:%M:%S')
        query   = "INSERT INTO Security_values (\"key\", \"value\", \"date\") VALUES ('%s', '%s', '%s')" % (mac, device_value, device_ctime)
        glogger.debug(Logs.OBJECTS, 'registrateObjectInMSDB:%s: query5: %s' % (pcnid, query))
        cursor = dbconn.cursor()
        cursor.execute(query)
        dbconn.commit()

        deviceId = str(msid)
        gps = "127,107,106,105,104,103,102,101,100,97,96,89,88,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,59,40,26"
        gsm = "127,108,107,106,105,104,103,102,101,100,97,96,89,88,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,59,48,47,46,45,40,26"
        balance = "127,120,110,108,107,106,105,104,103,102,101,100,98,97,96,95,94,93,92,91,89,88,87,86,85,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,62,61,60,59,57,56,55,54,53,52,51,50,49,48,47,46,45,44,43,40,39,35,34,33,32,31,30,29,28,27,26,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1"
        brk = "120,110,108,107,106,105,104,103,102,101,100,98,97,96,95,94,93,92,91,89,88,87,86,85,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,62,61,60,59,57,56,55,54,53,52,51,50,49,44,43,40,35,34,33,32,31,26,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1"
        cam1 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        cam2 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        cam3 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        cam4 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        cam5 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        cam6 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        cam7 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        cam8 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        gpsOff = "False"
        gsmOff = "False"
        brkOff = "False"
        balanceOff = "False"
        changeProgramMemory = "False"
        changeProgramGsm = "False"
        nameProgramFilePgsm = "CID_1240argus_TX9.bin"
        levelOff = "99" 
        levelBuff = "108,107,106,105,104,103,102,101,100,97,96,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,62,61,60,59,40,36,33,32,31,26"
        levelActivTCP ="94"
        levelActivGPRS = "91,90,89,88,87,86,48,47,46,45,39,35,34,29,27,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5"
        levelDeactivGPRS = "127,126,125,124,123,122,121,120,115,114,113,112,111,110,98,95,93,92,85,84,58,57,56,55,54,53,52,51,50,49,44,43,41,38,37,30,28,25,24,4,3,2,1"
        levelDeactivGPRSRoam = "42"
        changeVoice = "False"
        voiceFile = "voice.bin"

        queryPrefix = "INSERT INTO _device_transfer (\"deviceId\", \"gps\", \"gsm\", \"balance\", \"brk\", \"cam1\", \"cam2\", \"cam3\", \"cam4\", \"cam5\", \"cam6\", \"cam7\", \"cam8\", \"gpsOff\", \"gsmOff\", \"brkOff\", \"balanceOff\", \"changeProgramMemory\", \"changeProgramGsm\", \"nameProgramFilePgsm\", \"levelOff\", \"levelBuff\", \"levelActivTCP\", \"levelActivGPRS\", \"levelDeactivGPRS\", \"levelDeactivGPRSRoam\", \"changeVoice\", \"voiceFile\") VALUES "
        querySuffix = "('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (deviceId, gps, gsm, balance, brk, cam1, cam2, cam3, cam4, cam5, cam6, cam7, cam8, gpsOff, gsmOff, brkOff, balanceOff, changeProgramMemory, changeProgramGsm, nameProgramFilePgsm, levelOff, levelBuff, levelActivTCP, levelActivGPRS, levelDeactivGPRS, levelDeactivGPRSRoam, changeVoice, voiceFile)
        query = queryPrefix + querySuffix
        cursor = dbconn.cursor()
        cursor.execute(query)
        dbconn.commit()

        return (pcnid, mac, msid, login, pwd)

    def objectReloadFirmware(self, dbconn, msid, programFilePgsm):
        cursor = dbconn.cursor()
        check_query = "SELECT deviceId, nameProgramFilePgsm FROM _device_transfer WHERE deviceId='%s'" % (str(msid))
        cursor.execute(check_query)
        check_res = cursor.fetchall()
        if len(check_res) > 0 and len(check_res[0]) > 0:
            query = "UPDATE _device_transfer SET nameProgramFilePgsm='%s',changeProgramGsm='True' WHERE deviceId='%s'" % (programFilePgsm, str(msid))
            cursor.execute(query)
            dbconn.commit()
        else:
            gps = "127,107,106,105,104,103,102,101,100,97,96,89,88,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,59,40,26"
            gsm = "127,108,107,106,105,104,103,102,101,100,97,96,89,88,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,59,48,47,46,45,40,26"
            balance = "127,120,110,108,107,106,105,104,103,102,101,100,98,97,96,95,94,93,92,91,89,88,87,86,85,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,62,61,60,59,57,56,55,54,53,52,51,50,49,48,47,46,45,44,43,40,39,35,34,33,32,31,30,29,28,27,26,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1"
            brk = "120,110,108,107,106,105,104,103,102,101,100,98,97,96,95,94,93,92,91,89,88,87,86,85,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,62,61,60,59,57,56,55,54,53,52,51,50,49,44,43,40,35,34,33,32,31,26,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1"
            cam1 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            cam2 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            cam3 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            cam4 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            cam5 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            cam6 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            cam7 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            cam8 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            gpsOff = "False"
            gsmOff = "False"
            brkOff = "False"
            balanceOff = "False"
            changeProgramMemory = "False"
            changeProgramGsm = "True"
            nameProgramFilePgsm = str(programFilePgsm)
            levelOff = "99" 
            levelBuff = "108,107,106,105,104,103,102,101,100,97,96,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,62,61,60,59,40,36,33,32,31,26"
            levelActivTCP ="94"
            levelActivGPRS = "91,90,89,88,87,86,48,47,46,45,39,35,34,29,27,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5"
            levelDeactivGPRS = "127,126,125,124,123,122,121,120,115,114,113,112,111,110,98,95,93,92,85,84,58,57,56,55,54,53,52,51,50,49,44,43,41,38,37,30,28,25,24,4,3,2,1"
            levelDeactivGPRSRoam = "42"
            changeVoice = "False"
            voiceFile = "voice.bin"

            queryPrefix = "INSERT INTO _device_transfer (\"deviceId\", \"gps\", \"gsm\", \"balance\", \"brk\", \"cam1\", \"cam2\", \"cam3\", \"cam4\", \"cam5\", \"cam6\", \"cam7\", \"cam8\", \"gpsOff\", \"gsmOff\", \"brkOff\", \"balanceOff\", \"changeProgramMemory\", \"changeProgramGsm\", \"nameProgramFilePgsm\", \"levelOff\", \"levelBuff\", \"levelActivTCP\", \"levelActivGPRS\", \"levelDeactivGPRS\", \"levelDeactivGPRSRoam\", \"changeVoice\", \"voiceFile\") VALUES "
            querySuffix = "('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (str(msid), gps, gsm, balance, brk, cam1, cam2, cam3, cam4, cam5, cam6, cam7, cam8, gpsOff, gsmOff, brkOff, balanceOff, changeProgramMemory, changeProgramGsm, nameProgramFilePgsm, levelOff, levelBuff, levelActivTCP, levelActivGPRS, levelDeactivGPRS, levelDeactivGPRSRoam, changeVoice, voiceFile)
            query = queryPrefix + querySuffix
            cursor = dbconn.cursor()
            cursor.execute(query)
            dbconn.commit()
        return True

    def objectsLoadUnregistered(self, dbconn, path):
        objects = {}
        cursor = dbconn.cursor()
        dirname = path + '\\' + datetime.datetime.now().strftime('%Y.%m.%d') + '\\-1\\notregister_fail\\'
        if os.path.isdir(dirname):
            for i in os.listdir(dirname):
                entry = {}
                entry['mac'] = i
                entry['path'] = dirname + i
                stat = os.stat(entry['path'])
                entry['modified'] = datetime.datetime.fromtimestamp(stat.st_mtime)
                entry['created'] = datetime.datetime.fromtimestamp(stat.st_ctime)
                query = "SELECT uid,id FROM _devices_internal WHERE uid='%s'" % (str(entry['mac']))
                cursor.execute(query)
                found = cursor.fetchall()
                if len(found) > 0 and len(found[0]) > 0:
                    entry['msid'] = str(found[0][1])
                objects[entry['mac']] = entry
        return objects
    def objectCountEventsByType(self, dbconn, msid, period):
        cursor = dbconn.cursor()
        query = "exec device_GetCountEventFromType %s" % (str(msid))
        if period != None:
            query += ", '%s', '%s'" % (period[0], period[1])
        cursor.execute(query)
        buff = cursor.fetchall()
        typecounts = {}
        for etype in buff:
            typecounts[int(etype[1])] = int(etype[2])
        return typecounts

class CarOnlineDB:
    def __init__(self, dbparams):
        '''
        params are:
        {
         'host':...,
         'port':...,
         'dbname':...,
         'user':...,
         'password':...,
        }
        '''
        self.dbparams = dbparams
        self.dbapi = CarOnlineDB_Templates()

    def getconnection(self):
        if MSDBDriver == "pymssql":
            return pymssql.connect(host=self.dbparams['host'] + ':' + self.dbparams['port'],
                                   database=self.dbparams['dbname'], user=self.dbparams['user'], password=self.dbparams['password'])
        if MSDBDriver == "pyodbc":
            return pyodbc.connect(driver='{SQL Server}', server=self.dbparams['host'], port=self.dbparams['port'],
                                  database=self.dbparams['dbname'], uid=self.dbparams['user'], pwd=self.dbparams['password'])
        glogger.error(Logs.MSDB, 'getconnection: no any db driver loaded!')
        raise RuntimeError('no any db driver loaded')

    def objectsLoad(self, objs, prefixes):
        return self.dbapi.objectsLoad(self.getconnection(), objs, prefixes)

    def objectsLoadUnregistered(self, path):
        return self.dbapi.objectsLoadUnregistered(self.getconnection(), path)

    def objectRename(self, oldlogin, newlogin):
        return self.dbapi.objectRename(self.getconnection(), oldlogin, newlogin)

    def objectCreate(self, pcnid, mac):
        return self.dbapi.objectCreate(self.getconnection(), pcnid, mac)

    def objectReloadFirmware(self, msid, filename):
        return self.dbapi.objectReloadFirmware(self.getconnection(), msid, filename)

    def objectCountEventsByType(self, msid, period):
        return self.dbapi.objectCountEventsByType(self.getconnection(), msid, period)



class CarOnlineAPI:
    def __init__(self, eventTypes={}, url='http://dev.car-online.ru/', daysBack=1):
        self.url = url
        self.eventTypes = eventTypes
        self.daysBack = daysBack

    @staticmethod
    def mergeevents(x, y):
        for k, v in y.iteritems():
            if x.has_key(k):
                if k == 'type' and v == '89' and x[k] != '89':
                    continue
                elif k == 'contactIdCode' and v == 'Z60' and x[k] != 'Z60':
                    continue
                x[k] = v ###TODO: or realy merge it??? 
            else:
                x[k] = v

    def forceInit(self, objects):
        for o in objects.values():
            try:
                if len(o.skey) > 0:
                    glogger.debug(Logs.MSAPI, 'forceInit: id(%s) has skey(%s)' % (o.targetid, o.skey))
                    continue
                url = self.url + '/api/v2?get=securityKey&login=%s&password=%s' % (o.caronline_login, o.caronline_password)
                glogger.debug(Logs.MSAPI, 'forceInit: id(%s): %s' % (o.targetid, url))

                lines = urllib.request.urlopen(url).readlines()
                for l in lines:
                    buff = l.decode('utf-8').replace('</skey>', '').split('<skey>')
                    for skey in buff[1:]:
                        o.skey = skey

                glogger.info(Logs.MSAPI, 'forceInit: id(%s) has skey(%s) <- %s/%s' % (o.targetid, o.skey, o.caronline_login, o.caronline_password))
            except Exception as err:
                glogger.error(Logs.MSAPI, 'forceInit: id(%s): %s' % (o.targetid, str(err)))

    def getLowerBound(self):
        today = datetime.datetime.today()
        yesterday = today - datetime.timedelta(days=self.daysBack)
        firstday = None
        if yesterday.day > 1:
            firstday = datetime.datetime(year=yesterday.year, month=yesterday.month, day=yesterday.day)
        else:
            firstday = datetime.datetime(year=yesterday.year, month=yesterday.month, day=1)
        firstdaystr = str(int(time.mktime(firstday.timetuple()))) + '000'
        return (firstday, firstdaystr)
        
    def forceGPSParams(self, objects):
        firstday, firstdaystr = self.getLowerBound()
        for o in objects.values():
            try:
                url = self.url + 'api/v2?get=gpslist&filter=valid&skey=%sbegin=%s' % (o.skey, firstdaystr)
                glogger.debug(Logs.MSAPI, 'forceGPSLocation: id(%s): %s' % (o.targetid, url))
                
                gpslist = xmltree.fromstring(urllib.request.urlopen(url).read())
                for gps in reversed(gpslist):
                    if len(gps.attrib['latitude']) == 0 or len(gps.attrib['longitude']) == 0:
                        continue
                    if gps.attrib['valid'] != 'true': continue
                    
                    o.gps_course = int(gps.attrib['cource'])
                    o.gps_latitude = gps.attrib['latitude']
                    o.gps_longitude = gps.attrib['longitude']
                    o.gps_speed = int(gps.attrib['speed'])
                    break
                    
            except Exception as err:
                glogger.error(Logs.MSAPI, 'forceGPSLocation: id(%s): %s' % (o.targetid, str(err)))
                continue

    def forceCanParams(self, objects):
        firstday, firstdaystr = self.getLowerBound()
        for o in objects.values():
            try:
                url = self.url + 'api/v2?get=canlist&skey=%s&begin=%s' % (o.skey, firstdaystr)
                glogger.debug(Logs.MSAPI, 'forceCanParams: id(%s): %s' % (o.targetid, url))
                
                canlist = xmltree.fromstring(urllib.request.urlopen(url).read())
                for can in reversed(canlist):
                    if len(can.attrib['strValue']) > 0:
                        canbuf = can.attrib['strValue']
                        canbuf = canbuf.split()
                        if len(canbuf) != 4: continue
                        
                        milage = canbuf[1]
                        rpm = canbuf[2]
                        code = canbuf[3]
                        
                        if milage[0] != 'C' or rpm[0] != 'H' or code[0] != 'P':
                            glogger.warning(Logs.MSAPI, 'forceCanParams: id(%s) changed api/canlist/strValue response: "%s"' % (o.targetid, can.attrib['strValue']))
                            
                        o.can_milage = int(milage[1:].split('.')[0])
                        o.can_engine_rpm = int(rpm[1:])
                        o.can_engine_code = code
                        o.can_engine_code_error = int(code[1:3])
                        o.can_engine_code_warn1 = int(code[3:5])
                        o.can_engine_code_warn2 = int(code[5:7])
                        
                        if o.can_engine_code_error & 0x01 == 0x01:
                            o.can_engine_error1 = True
                        else:
                            o.can_engine_error1 = False
                            
                        if o.can_engine_code_error & 0x02 == 0x02:
                            o.can_engine_error2 = True
                        else:
                            o.can_engine_error2 = False
                            
                        if o.can_engine_code_error & 0x04 == 0x04:
                            o.can_engine_error3 = True
                        else:
                            o.can_engine_error3 = False
                            
                        if o.can_engine_code_error & 0x08 == 0x08:
                            o.can_engine_error4 = True
                        else:
                            o.can_engine_error4 = False
                        
                        if o.can_engine_code_error & 0x10 == 0x10:
                            o.can_engine_error5 = True
                        else:
                            o.can_engine_error5 = False
                            
                        if o.can_engine_code_error & 0x20 == 0x20:
                            o.can_engine_error6 = True
                        else:
                            o.can_engine_error6 = False
                            
                        
                        if o.can_engine_code_warn1 & 0x01 == 0x01:
                            o.can_engine_warn11 = True
                        else:
                            o.can_engine_warn11 = False
                            
                        if o.can_engine_code_warn1 & 0x02 == 0x02:
                            o.can_engine_warn12 = True
                        else:
                            o.can_engine_warn12 = False
                            
                        if o.can_engine_code_warn1 & 0x04 == 0x04:
                            o.can_engine_warn13 = True
                        else:
                            o.can_engine_warn13 = False
                            
                        if o.can_engine_code_warn1 & 0x08 == 0x08:
                            o.can_engine_warn14 = True
                        else:
                            o.can_engine_warn14 = False
                        
                        if o.can_engine_code_warn1 & 0x10 == 0x10:
                            o.can_engine_warn15 = True
                        else:
                            o.can_engine_warn15 = False
                            
                        if o.can_engine_code_warn1 & 0x20 == 0x20:
                            o.can_engine_warn16 = True
                        else:
                            o.can_engine_warn16 = False
                            
                        if o.can_engine_code_warn1 & 0x40 == 0x40:
                            o.can_engine_warn17 = True
                        else:
                            o.can_engine_warn17 = False

                        if o.can_engine_code_warn2 & 0x01 == 0x01:
                            o.can_engine_warn21 = True
                        else:
                            o.can_engine_warn21 = False

                        if o.can_engine_code_warn2 & 0x02 == 0x02:
                            o.can_engine_warnw2 = True
                        else:
                            o.can_engine_warn22 = False
                            
                        if o.can_engine_code_warn2 & 0x04 == 0x04:
                            o.can_engine_warn23 = True
                        else:
                            o.can_engine_warn23 = False
                            
                        if o.can_engine_code_warn2 & 0x08 == 0x08:
                            o.can_engine_warn24 = True
                        else:
                            o.can_engine_warn24 = False
                            
                        break
            except Exception as err:
                glogger.error(Logs.MSAPI, 'forceCanParams: id(%s): %s' % (o.targetid, str(err)))
        
    def getPGSMEvents(self, processed, processing, objects):
        firstday, firstdaystr = self.getLowerBound()
        totalloaded = 0
        for o in objects.values():
            try:
                loaded = 0
                if len(o.skey) == 0:
                    raise RuntimeError('CarOnlineAPI::getPGSMEvents: id(%s) has no skey' % (o.targetid))
                url = self.url + 'api/v2?get=events&skey=%s&begin=%s' % (o.skey, firstdaystr)

                docs = urllib.request.urlopen(url).read()
                for l in docs.split('<event ')[1:]:
                    try:
                        l = l.replace('</eventlist>', '').replace('/>', '').replace('"', '')
                        event = {}

                        for part in l.split():
                            part = part.split('=')
                            if len(part[0]) > 0 and len(part[1]) > 0:
                                if part[0] == 'eventTime':
                                    event[part[0]] = datetime.datetime.fromtimestamp(int(part[1][:10]))
                                else:
                                    event[part[0]] = part[1]

                        event['objectId'] = o.targetid
                        eventid = int(event['eventId'])
                        eventtype = int(event['type'])

                        if eventtype not in self.eventTypes: continue
                        event['contactIdCode'] = str(self.eventTypes[eventtype])
                        if eventtype == 20 or eventtype == 54: event['credoZoneCode'] = '1'
                        elif eventtype == 21 or eventtype == 55: event['credoZoneCode'] = '2'
                        elif eventtype == 22 or eventtype == 56: event['credoZoneCode'] = '3'
                        else: event['credoZoneCode'] = '0'

                        if 'e_%d' % eventid not in processed:
                            if event['eventTime'] - firstday > datetime.timedelta(seconds=1):
                                if o.events.has_key(eventid):
                                    CarOnlineAPI.mergeevents(o.events[eventid], event)
                                else:
                                    o.events[eventid] = event
                                loaded += 1
                                totalloaded += 1
                            processing.add('e_%d' % eventid)

                    except Exception as err:
                        glogger.warning(Logs.MSAPI, 'getPGSMEvents: id(%s) line(%s): %s' % (o.targetid, str(l), str(err)))
                        continue
                glogger.debug(Logs.MSAPI, 'getPGSMEvents: id(%s) events(%d/%d): %s' % (o.targetid, loaded, len(o.events), url))

            except Exception as err:
                glogger.error(Logs.MSAPI, 'getPGSMEvents: id(%s): %s' % (o.targetid, str(err)))
                continue
        glogger.info(Logs.MSAPI, 'getPGSMEvents: %d received' % totalloaded)
    def getContactIdEvents(self, processed, processing, objects):
        firstday, firstdaystr = self.getLowerBound()
        totalloaded = 0
        for o in objects.values():
            try:
                loaded = 0
                if len(o.skey) == 0:
                    raise RuntimeError('CarOnlineAPI::getContactIdEvents: id(%s) has no skey' % (o.targetid))
                url = self.url + 'api/v2?get=contactId&skey=%s&begin=%s' % (o.skey, firstdaystr)

                docs = urllib.request.urlopen(url).read()
                #for l in docs: pass

                glogger.debug(Logs.MSAPI, 'getContactIdEvents: id(%s) events(%d/%d): %s' % (o.targetid, loaded, len(o.events), url))

            except Exception as err:
                glogger.error(Logs.MSAPI, 'getContactIdEvents: id(%s): %s' % (o.targetid, str(err)))
                continue
        glogger.info(Logs.MSAPI, 'getContactIdEvents: %d received' % totalloaded)

    def getPhotos(self, processed, processing, objects):
        firstday, firstdaystr = self.getLowerBound()
        totalphotosloaded = 0
        for o in objects.values():
            try:
                loaded = 0
                if len(o.skey) == 0:
                    raise RuntimeError('CarOnlineAPI::getPhotos: id(%s) has no skey' % (o.targetid))
                url = self.url + 'api/v2?get=photos&skey=%s&begin=%s' % (o.skey, firstdaystr)

                docs = urllib.request.urlopen(url).read()
                for l in docs.split('<photo ')[1:]:
                    try:
                        l = l.replace('</photolist>', '').replace('/>', '').replace('"', '')
                        photo = {}

                        for part in l.split():
                            part = part.split('=')
                            if len(part[0]) > 0 and len(part[1]) > 0:
                                if part[0] == 'eventTime':
                                    photo[part[0]] = datetime.datetime.fromtimestamp(int(part[1][:10]))
                                else:
                                    photo[part[0]] = part[1]

                        photo['skey'] = o.skey
                        photo['objectId'] = o.targetid
                        photo['isPhoto'] = True
                        photo['photoId'] = photo['id']
                        eventid = int(photo['eventId'])
                        photo['contactIdCode'] = self.eventTypes[89]
                        photo['credoZoneCode'] = '4'

                        if 'f_%d' % eventid not in processed:
                            if photo['eventTime'] - firstday > datetime.timedelta(seconds=1):
                                if o.events.has_key(eventid):
                                    CarOnlineAPI.mergeevents(o.events[eventid], photo)
                                else:
                                    o.events[eventid] = photo
                                loaded += 1
                                totalphotosloaded += 1
                            processing.add('f_%d' % eventid)

                    except Exception as err:
                        glogger.warning(Logs.MSAPI, 'getPhotos: id(%s) line(%s): %s' % (o.targetid, str(l), str(err)))
                        continue
                glogger.debug(Logs.MSAPI, 'getPhotos: id(%s) photos(%d/%d): %s' % (o.targetid, loaded, len(o.events), url))


            except Exception as err:
                glogger.error(Logs.MSAPI, 'getPhotos: id(%s): %s' % (o.targetid, str(err)))
                continue
        glogger.info(Logs.MSAPI, 'getPhotos: %d received' % totalphotosloaded)

try:
    import pymssql
except:
    glogger.gerror('pymssql is not imported!')

class CredoDB:
    def __init__(self, params):
        self.params = params
    def connection(self):
        self.params['database'] = 'pult4db'
        pult4db = pymssql.connect(**self.params)
        glogger.debug(Logs.CREDODB, 'connected to "%s"' % str(self.params))

        self.params['database'] = 'pult4db_archives'
        pult4db_archives = pymssql.connect(**self.params)
        glogger.debug(Logs.CREDODB, 'connected to "%s"' % str(self.params))

        self.params['database'] = 'pult4db_videoarchives'
        pult4db_videoarchives = pymssql.connect(**self.params)
        glogger.debug(Logs.CREDODB, 'connected to "%s"' % str(self.params))

        return (pult4db, pult4db_archives, pult4db_videoarchives)

    def writeEvent(self, event):
        connection = self.connection()
        cursor0 = connection[0].cursor()
        cursor1 = connection[1].cursor()
        cursor2 = connection[2].cursor()

        dbtable1 = 'archive' + datetime.datetime.today().strftime('%Y%m') + '01'
        getEventId1 = 'SELECT MAX(event_id) FROM %s' % dbtable1
        cursor1.execute(getEventId1)

        getEventId0 = 'SELECT MAX(event_id) FROM temp'
        cursor0.execute(getEventId0)

        eventId = str(max(int(cursor1.fetchall()[0][0]) + 1, int(cursor0.fetchall()[0][0] + 1)))

        if event.has_key('type') and event['type'] != '24' and event['type'] != '25' and event['type'] != '26':
            #putTemp =  "INSERT INTO temp (event_id, panel_id, group_, line, zone, code, codegroup, timeevent, receiver, phone, metercount,stateevent,event_parent_id,date_key,priority,computer,bitmask) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (eventId, event['objectId'], '1', 'V201', '2', str(event['contactIdCode']), '1', event['eventTime'].strftime('%Y-%m-%d %H:%M:%S') + '.000', '0', 'NULL', 'NULL', '0', eventId, event['eventTime'].strftime('%Y%m%d'), '2', 'NULL', '0')
            if event.has_key('isPhoto') and event['isPhoto'] == True:
                putTemp =  "INSERT INTO temp (event_id, panel_id, group_, line, zone, code, codegroup, timeevent,stateevent,date_key,priority,bitmask,metercount) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (eventId, event['objectId'], '1', 'V201', str(event['credoZoneCode']), str(event['contactIdCode']), '1', event['eventTime'].strftime('%Y-%m-%d %H:%M:%S') + '.000', '0', event['eventTime'].strftime('%Y%m%d'), '2', '0', 'camera1')
            else:
                putTemp =  "INSERT INTO temp (event_id, panel_id, group_, line, zone, code, codegroup, timeevent, stateevent,date_key,priority,bitmask) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (eventId, event['objectId'], '1', 'V201', str(event['credoZoneCode']), str(event['contactIdCode']), '1', event['eventTime'].strftime('%Y-%m-%d %H:%M:%S') + '.000', '0', event['eventTime'].strftime('%Y%m%d'), '2', '0')
            glogger.debug(Logs.CREDODB, 'writeEvent: putTemp(%s)' % putTemp)
            cursor0.execute(putTemp)

        if event.has_key('isPhoto') and event['isPhoto'] == True:
            putEvent = "INSERT INTO %s (event_id, date_key, panel_id, group_, line, zone, code, codegroup, timeevent, stateevent, bitmask,metercount) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (dbtable1, eventId, event['eventTime'].strftime('%Y%m%d'), event['objectId'], '0', 'V201', str(event['credoZoneCode']), str(event['contactIdCode']), '1', event['eventTime'].strftime('%Y-%m-%d %H:%M:%S') + '.000', '0', '0', 'camera1')
        else:
            putEvent = "INSERT INTO %s (event_id, date_key, panel_id, group_, line, zone, code, codegroup, timeevent, stateevent, bitmask) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (dbtable1, eventId, event['eventTime'].strftime('%Y%m%d'), event['objectId'], '0', 'V201', str(event['credoZoneCode']), str(event['contactIdCode']), '1', event['eventTime'].strftime('%Y-%m-%d %H:%M:%S') + '.000', '0', '0')
        glogger.debug(Logs.CREDODB, 'writeEvent: putEvent(%s)' % putEvent)
        cursor1.execute(putEvent)

        updateCurrentId = "UPDATE archiveid SET current_id='%s'" % eventId
        cursor0.execute(updateCurrentId)
        glogger.debug(Logs.CREDODB, 'writeEvent: archiveid.current_id updated to "%s"' % eventId)

        if event.has_key('isPhoto') and event['isPhoto'] == True:
            dbtable2 = 'videoarchive' + datetime.datetime.today().strftime('%Y%m') + '01'

            url = 'http://dev.car-online.ru/api/v2?get=photo&skey=%s&id=%s&binary' % (event['skey'], event['photoId'])
            glogger.debug(Logs.CREDODB, 'writeEvent: photourl(%s)' % url)
            tmp = open('C:\\Magic\\tmp.image', 'wb')
            tmp.write(urllib.request.urlopen(url).read())
            tmp.close()

            getPictureId    = 'SELECT MAX(pictureId) FROM %s' % dbtable2
            cursor2.execute(getPictureId)
            pictureId       = str(int(cursor2.fetchall()[0][0]) + 1)

            putPicture= "EXEC [pult4db_videoarchives].[dbo].[image_AddFile] @fileName='\\Magic\\tmp.image', @pictureId='%s', @objectId='%s', @videoZone='1', @Panel_Id='%s', @Result_Id='1', @event_id='%s', @TimeEvent='%s', @Date_Key='%s', @BitMask='0'" % (pictureId, event['objectId'], event['objectId'], eventId, event['eventTime'].strftime('%Y-%m-%d %H:%M:%S') + '.000', event['eventTime'].strftime('%Y%m%d'))
            glogger.debug(Logs.CREDODB, 'writeEvent: putPicture(%s)' % putPicture)
            cursor2.execute(putPicture)
            connection[2].commit()
        connection[1].commit()
        connection[0].commit()

    def writeEvents(self, processing, objects):
        totalwritten = 0
        for o in objects.values():
            try:
                written = 0
                failed = 0
                for e in o.events.values():
                    try:
                        if e.has_key('isPhoto') and e['isPhoto'] == True:
                            if e['contactIdCode'] != 'Z60':
                                self.writeEvent(e)
                            e['contactIdCode'] = 'Z60'
                            self.writeEvent(e)
                        else:
                            self.writeEvent(e)

                        written += 1
                        totalwritten += 1

                    except Exception as err:
                        glogger.warning(Logs.CREDODB, 'writeEvents: id(%s) eventid(%s) event(%s): %s' % (o.targetid, e['eventId'], str(e), str(err)))
                        if 'e_' + e['eventId'] in processing: processing.remove('e_' + e['eventId'])
                        if 'f_' + e['eventId'] in processing: processing.remove('f_' + e['eventId'])
                        failed += 1
                        continue

                glogger.info(Logs.CREDODB, 'writeEvent: id(%s) %d/%d written, %d errors' % (o.targetid, written, len(o.events), failed))
                o.events.clear()

            except Exception as err:
                glogger.error(Logs.CREDODB, 'writeEvents: id(%s): %s' % (o.targetid, str(err)))
                continue
        glogger.info(Logs.CREDODB, 'writeEvents: %d written' % (totalwritten))
